﻿# -*- coding:utf-8 -*-
import sqlite3
# 打开学生数据库
def user_opendb():
        conn = sqlite3.connect("info.db")
        cur = conn.execute("""create table if not exists dian_can_info(
        id integer PRIMARY KEY autoincrement,
        name varchar(12),
        nian_ji varchar(10),
        zao_numbers int,
        wu_numbers int,
        wan_numbers int,
        day varchar(12),
        week varchar(4),
        time varchar(20)
        )""")
        return cur, conn
    
#查询所有列名
def user_lie_name():
    hel = user_opendb()
    cur = hel[1].cursor()
    cur.execute("select * from dian_can_info")
    col_name_list = [tuple[0] for tuple in cur.description]  
    return col_name_list
    cur.close()

#查询学生全部信息
def user_slectTable():
        hel = user_opendb()
        cur = hel[1].cursor()
        cur.execute("select * from dian_can_info")
        res = cur.fetchall()
        return res
        cur.close()
#  往学生数据库中添加内容
def user_insertData(name,nian_ji,zao_numbers,wu_numbers,wan_numbers,day,week,time):
        hel = user_opendb()
        hel[1].execute("insert into dian_can_info (name,nian_ji,zao_numbers,wu_numbers,wan_numbers,day,week,time) values (?,?,?,?,?,?,?,?)",(name,nian_ji,zao_numbers,wu_numbers,wan_numbers,day,week,time))
        hel[1].commit()
        hel[1].close()
#查询学生个人信息
def user_showdb(number):
        hel = user_opendb()
        cur = hel[1].cursor()
        cur.execute("select * from dian_can_info where student_number="+number)
        res = cur.fetchone()
        cur.close()
        return res
#   删除学生数据库中的全部内容
def user_delalldb():
        hel = user_opendb()              # 返回游标conn
        hel[1].execute("delete from dian_can_info")
        hel[1].commit()
        hel[1].close()

#   删除学生数据库中的指定内容
def user_deldb(number):
        hel = user_opendb()              # 返回游标conn
        hel[1].execute("delete from dian_can_info where student_number="+str(number))
        print("已删除学号为 %s 学生" %number)
        hel[1].commit()
        hel[1].close()
        
#  修改学生数据库的内容
def user_alter(id,zao_number,wu_number,wan_number,time):
        print(id,zao_number,wu_number,wan_number,time)
        hel = user_opendb()
        hel[1].execute("update dian_can_info set zao_numbers= '%i' , wu_numbers= '%i',wan_numbers= '%i',time = '%s' where id = '%i'"%(zao_number,wu_number,wan_number,time,id))
        hel[1].commit()
        hel[1].close()
        
#  修改学生数据库密码的内容
def user_alter_pw(number,pw):
    hel = user_opendb()
    hel[1].execute("update dian_can_info set student_passworld= %s where student_number=%s"%(pw,number))
    hel[1].commit()
    hel[1].close()
        
# 登录查询学生数据
def user_slect_name_day(name,day):
        hel = user_opendb()
        cur = hel[1].cursor()
        cur.execute("select * from dian_can_info where name like '%%%s%%' and day like '%%%s%%'"%(name,day))
        res = cur.fetchall()
        return res
        cur.close()

# xing = [
# '赵', '钱', '孙', '李', '周', '吴', '郑', '王', '冯', '陈', '褚', '卫', '蒋', '沈', '韩', '杨', '朱', '秦', '尤', '许',
# '何', '吕', '施', '张', '孔']

# ming = [
# '钱', '紶','语', '元', '喜', '曾', '离', '飞', '科', '言', '干', '流', '欢', '约', '各', '即', '指', '合', '反',
# '题', '必', '该', '论', '交', '终', '林', '请', '医', '晚', '制', '球', '决', '窢', '传', '画', '保', '读', '运', '及',
# '则', '房', '早', '院', '量', '苦', '火', '布', '品', '近', '坐', '产', '答', '星', '精', '视', '五', '连', '司', '巴',
# '乾', '坤']
# import random,time

# for i in range(1,500):
#         x   =   random.choice(xing)
#         m1  =   random.choice(ming)
#         m2  =   random.choice(ming)
#         name=x+m1+m2
#         nian_ji=random.choice(['七年级','八年级','九年级','高一','高二','高三'])
#         zao_numbers=random.choice([1,2,1,2,1,2,0,1,0])
#         wu_numbers=random.choice([1,2,1,2,1,2,0,1,0])
#         wan_numbers=random.choice([1,2,1,2,1,2,0,1,0])
#         day='2021-0%s-0%s'%(random.choice([1,2,3,4,5,6,7,8,9]),random.choice([1,2,3,4,5,6,7,8,9]))
#         week=random.choice(['星期一','星期二','星期三','星期四','星期五','星期六','星期日'])
#         times='2021.03.06-14:43:03-星期六'
#         user_insertData(name,nian_ji,zao_numbers,wu_numbers,wan_numbers,day,week,times)
#         time.sleep(0.1)
# print()